#!/bin/bash
source /etc/profile


generate_awk(){
  table=$1
  echo table:$table
  keys='';values='';count=0;update=''
  databases='sqlserver'
  if [[ $table == TBL_ORDERDETAILS ]]
   then
    databases='oracleanditem'
   elif [[ $table == TBL_ORDERDETAILS_CD  ]]
   then
    databases='oraclecd'
    table='TBL_ORDERDETAILS'
  fi
  for i in $(mysql -uroot -proot -hhadoop2 -e "desc $databases.$table"|awk 'NR != 1 {print $1","$2}')
  do
    line=($(echo $i|awk -F',' '{print $1,$2}'))
    count=$(expr $count + 1)
    key=${line[0]};is_num=${line[1]};
    flag=0
    if [[ $is_num == *decimal* ]] || [[ $is_num == *int* ]] || [[ $is_num == *double* ]] || [[ $is_num == *float* ]]
      then
          flag=1
    fi
    if [[ $flag == '1' ]]
      then
        values="$values\",\"\$$count"
    else
        values="$values\",\"\"trim('\"\$$count\"')\""
    fi
    if [[ $key == station_id ]] || [[ $key == STATIONCODE ]]
      then
        key="'80004'"
    elif [[ $key == create_shijian ]]
      then
        key="'2019-02-21 11:00'"
    fi
    keys="$keys,$key"
    update="$update,$key=\$$count"
   done

  keys=${keys#,};values=${values#*\",\"};update=${update#,}
#  echo "#! /usr/bin/awk -f">$path/awk/$table.awk
#  echo "{if(NR!=1) print \"replace into mid.$table($keys) values(\"$values\");\"}">>$path/awk/$table.awk
#  echo "select $keys from $table">$path/sql/select/select_$table.sql
  cat >>d80004replace.sql<<EOF
REPLACE into $table
SELECT $keys
from $table
where station_id='D80004';

EOF
cat >>d80004delete.sql<<EOF
DELETE from $table
where trim(station_id)='D80004' ;

EOF

}

#遍历result库，循环生成sql和awk脚本
for table in $(cat ../hive/midtable.txt)
do
  generate_awk $table
#cat >$path/main/$table"_main.sh"<<EOF
##!/bin/bash
#
#base_path=\$(dirname \$0)
##实时处理 $table 表
#sh \${base_path}/../sql/select/${table}_select.sh  |awk -F '\t' -f \${base_path}/../awk/${table}.awk >\${base_path}/../sql/replace/${table}_replace.sql
#mysql -hhadoop3 -uroot  -proot <\${base_path}/../sql/replace/${table}_replace.sql
#EOF

done
